use emode_sauvegarde
go

--agg_yr_qt_rn_st_ln_ca_sr
Insert into AGG_YR_QT_RN_ST_LN_CA_SR
  (YEAR,QUARTER,STATE,LINE,CATEGORY,SALES_REVENUE)
Select
  cyl.year
  , 'Q' + convert(varchar(1),cyl.quarter) as quarter
  , ol.state
  , al.family_name
  , al.category
  , sum(sf.amount_sold) as sales_revenue
From
  dbo.calendar_year_lookup cyl inner join shop_facts sf
    on cyl.week_key = sf.week_key
      inner join outlet_lookup ol
    on ol.shop_code = sf.shop_code
      inner join article_lookup al
    on al.article_code = sf.article_code
Group by
    cyl.year
    , 'Q' + convert(varchar(1),cyl.quarter)
    , ol.state
    , al.family_name
    , al.category
Order by
    1,2,3,4,5,6;
    
--agg_yr_thru_sn_sr_qt_ma
Insert into AGG_YR_THRU_SN_SR_QT_MA
  (YEAR,QUARTER,MONTH,MONTH_NAME,WEEK,CITY,STORE_NAME,SALES_REVENUE,QUANTITY_SOLD,MARGIN)
Select
  cyl.year
  , 'Q' + convert(varchar(1),cyl.quarter) as quarter
  , cyl.month
  , cyl.month_name
  , cyl.week_in_year as week
  , ol.city
  , ol.shop_name as store_name
  , sum(sf.amount_sold) as sales_revenue
  , sum(sf.quantity_sold) as quantity_sold
  , sum(sf.margin) as margin
From
  calendar_year_lookup cyl inner join shop_facts sf
    on cyl.week_key = sf.week_key
      inner join outlet_lookup ol
    on ol.shop_code = sf.shop_code
Group by
  cyl.year
  , 'Q' + convert(varchar(1),cyl.quarter)
  , cyl.month
  , cyl.month_name
  , cyl.week_in_year
  , ol.city
  , ol.shop_name
Order by
    1,2,3,4,5,6,7,8,9,10;